﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Aliyeye.Car.Net.Utility;

namespace Aliyeye.Car.Net.Dal
{
    public class Accounts_Users
    {
        public Accounts_Users()
        { }
        public int Add(Model.Accounts_Users model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
					new SqlParameter("@Password", SqlDbType.Binary,50),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@DepID", SqlDbType.Int,4),
					new SqlParameter("@Sex", SqlDbType.NVarChar,50),
					new SqlParameter("@Birth", SqlDbType.NVarChar,50),
					new SqlParameter("@Addr", SqlDbType.NVarChar,200),
					new SqlParameter("@Tel", SqlDbType.NVarChar,50),
					new SqlParameter("@Email", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.NVarChar,50),
					new SqlParameter("@School", SqlDbType.NVarChar,200),
					new SqlParameter("@InputTime", SqlDbType.DateTime),
					new SqlParameter("@DriveTime", SqlDbType.DateTime),
					new SqlParameter("@HomeTel", SqlDbType.NVarChar,50),
					new SqlParameter("@State", SqlDbType.Bit,1),
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@CarType", SqlDbType.NVarChar,50),
					new SqlParameter("@UserImage", SqlDbType.Image),
					new SqlParameter("@DriverImage", SqlDbType.Image)};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.UserName;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.TrueName;
            parameters[4].Value = model.DepID;
            parameters[5].Value = model.Sex;
            parameters[6].Value = model.Birth;
            parameters[7].Value = model.Addr;
            parameters[8].Value = model.Tel;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.QQ;
            parameters[11].Value = model.School;
            parameters[12].Value = model.InputTime;
            parameters[13].Value = model.DriveTime;
            parameters[14].Value = model.HomeTel;
            parameters[15].Value = model.State;
            parameters[16].Value = model.RoleId;
            parameters[17].Value = model.CarType;
            parameters[18].Value = model.UserImage;
            parameters[19].Value = model.DriverImage;

            int roleId = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Accounts_Users_ADD", parameters);
            if (roleId > 0)
            {
                return roleId;
            }
            else
            {
                return 0;
            }
        }
        public bool Update(Model.Accounts_Users model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
					new SqlParameter("@Password", SqlDbType.Binary,50),
					new SqlParameter("@TrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@DepID", SqlDbType.Int,4),
					new SqlParameter("@Sex", SqlDbType.NVarChar,50),
					new SqlParameter("@Birth", SqlDbType.NVarChar,50),
					new SqlParameter("@Addr", SqlDbType.NVarChar,200),
					new SqlParameter("@Tel", SqlDbType.NVarChar,50),
					new SqlParameter("@Email", SqlDbType.NVarChar,50),
					new SqlParameter("@QQ", SqlDbType.NVarChar,50),
					new SqlParameter("@School", SqlDbType.NVarChar,200),
					new SqlParameter("@InputTime", SqlDbType.DateTime),
					new SqlParameter("@DriveTime", SqlDbType.DateTime),
					new SqlParameter("@HomeTel", SqlDbType.NVarChar,50),
					new SqlParameter("@State", SqlDbType.Bit,1),
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@CarType", SqlDbType.NVarChar,50),
					new SqlParameter("@UserImage", SqlDbType.Image),
					new SqlParameter("@DriverImage", SqlDbType.Image)};
            parameters[0].Value = model.UserID;
            parameters[1].Value = model.UserName;
            parameters[2].Value = model.Password;
            parameters[3].Value = model.TrueName;
            parameters[4].Value = model.DepID;
            parameters[5].Value = model.Sex;
            parameters[6].Value = model.Birth;
            parameters[7].Value = model.Addr;
            parameters[8].Value = model.Tel;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.QQ;
            parameters[11].Value = model.School;
            parameters[12].Value = model.InputTime;
            parameters[13].Value = model.DriveTime;
            parameters[14].Value = model.HomeTel;
            parameters[15].Value = model.State;
            parameters[16].Value = model.RoleId;
            parameters[17].Value = model.CarType;
            parameters[18].Value = model.UserImage;
            parameters[19].Value = model.DriverImage;

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Accounts_Users_Update", parameters);
            if (intResult > 0)
                return true;
            else
                return false;
        }

        public int CheckLogin(string UserName, string Password)
        {
            int rowsAffected;
            byte[] pass = JhEncrypt.EncryptPassword(Password);
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
					new SqlParameter("@Password", SqlDbType.Binary,50),
                                         };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = UserName;
            parameters[2].Value = pass;

            try
            {
                int result = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Accounts_Users_CheckLogin", parameters);
                return Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception)
            {
                return 0;
            }
            // StringBuilder strSql = new StringBuilder();Accounts_Users_CheckLogin
            //strSql.Append("update Accounts_Users set UserID=@UserID,UserName=@,Password,TrueName,DepID,Sex,Birth,Addr,Tel,Email,QQ,School,InputTime,DriveTime,HomeTel,State,RoleId ");
            //strSql.Append(" FROM Accounts_Users ");

        }
        public bool UpdatePass(Model.Accounts_Users model)
        {
            int intResult = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Accounts_Users set ");
            strSql.Append("Password=@Password ");
            strSql.Append(" where UserID=@UserID ");
            SqlParameter[] parameters = { new SqlParameter("@Password", SqlDbType.Binary, 50), new SqlParameter("@UserID", SqlDbType.Int, 4) };
            parameters[0].Value = model.Password;
            parameters[1].Value = model.UserID;
            if (model.NewPassword != null)
            {
                strSql.Append(" and Password=@OldPassword");
                SqlParameter[] parameters2 = { new SqlParameter("@Password", SqlDbType.Binary, 50), new SqlParameter("@UserID", SqlDbType.Int, 4), new SqlParameter("@OldPassword", SqlDbType.Binary, 50) };
                parameters2[0].Value = model.NewPassword;
                parameters2[1].Value = model.UserID;
                parameters2[2].Value = model.Password;

                intResult = SqlHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters2);
            }
            else
            {
                intResult = SqlHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            }
            if (intResult > 0)
                return true;
            else
                return false;
        }
        public bool Delete(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)};
            parameters[0].Value = Id;
            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Accounts_Users_Delete", parameters);
            if (intResult > 0)
                return true;
            else
                return false;
        }
        public Model.Accounts_Users GetModel(int UserID)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserID", SqlDbType.Int,4)};
            parameters[0].Value = UserID;

            Model.Accounts_Users model = new Model.Accounts_Users();
            DataSet ds = SqlHelper.GetDataFormDataSet(CommandType.StoredProcedure, "Accounts_Users_GetModel", parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                if (ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = (byte[])ds.Tables[0].Rows[0]["Password"];
                }
                model.TrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                if (ds.Tables[0].Rows[0]["DepID"].ToString() != "")
                {
                    model.DepID = int.Parse(ds.Tables[0].Rows[0]["DepID"].ToString());
                }
                model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
                model.Birth = ds.Tables[0].Rows[0]["Birth"].ToString();
                model.Addr = ds.Tables[0].Rows[0]["Addr"].ToString();
                model.Tel = ds.Tables[0].Rows[0]["Tel"].ToString();
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                model.School = ds.Tables[0].Rows[0]["School"].ToString();
                if (ds.Tables[0].Rows[0]["InputTime"].ToString() != "")
                {
                    model.InputTime = DateTime.Parse(ds.Tables[0].Rows[0]["InputTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DriveTime"].ToString() != "")
                {
                    model.DriveTime = DateTime.Parse(ds.Tables[0].Rows[0]["DriveTime"].ToString());
                }
                model.HomeTel = ds.Tables[0].Rows[0]["HomeTel"].ToString();
                if (ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["State"].ToString() == "1") || (ds.Tables[0].Rows[0]["State"].ToString().ToLower() == "true"))
                    {
                        model.State = true;
                    }
                    else
                    {
                        model.State = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                {
                    model.RoleId = int.Parse(ds.Tables[0].Rows[0]["RoleId"].ToString());
                }
                model.CarType = ds.Tables[0].Rows[0]["CarType"].ToString();
                model.UserImage = ds.Tables[0].Rows[0]["UserImage"] == DBNull.Value ? null : (byte[])ds.Tables[0].Rows[0]["UserImage"];
                model.DriverImage = ds.Tables[0].Rows[0]["DriverImage"] == DBNull.Value ? null : (byte[])ds.Tables[0].Rows[0]["DriverImage"];

                if (ds.Tables[0].Rows[0]["CustomID"].ToString() != "")
                {
                    model.CustomID = int.Parse(ds.Tables[0].Rows[0]["CustomID"].ToString());
                }
                else
                {
                    model.CustomID = 0;
                }
                model.CustomerCode = ds.Tables[0].Rows[0]["CustomerCode"].ToString();
                model.CustomerName = ds.Tables[0].Rows[0]["CustomerName"].ToString();
                model.RoleName = ds.Tables[0].Rows[0]["RoleName"].ToString();

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserID,UserName,Password,TrueName,DepID,Sex,Birth,Addr,Tel,Email,QQ,School,InputTime,DriveTime,HomeTel,State,RoleId,CarType ");
            strSql.Append(" FROM Accounts_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetDriveList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserID,UserName,Password,TrueName,DepID,Sex,Birth,Addr,Tel,Email,QQ,School,InputTime,DriveTime,HomeTel,State,CarType");
            strSql.Append(" from  Accounts_Users left join  Accounts_Roles on Accounts_Users.RoleID=Accounts_Roles.RoleID");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where Accounts_Roles.Description='驾驶员'" + "And" + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetDriveLists(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserID,UserName,Password,TrueName,DepID,Sex,Birth,Addr,Tel,Email,QQ,School,InputTime,DriveTime,HomeTel,State,CarType");
            strSql.Append(" from  Accounts_Users  left join  Accounts_Roles on Accounts_Users.RoleID=Accounts_Roles.RoleID");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where Accounts_Roles.Description='驾驶员'" + "And" + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }

        public Model.Accounts_Users GetDriveModel(int UserID)
        {
            string strWhere = "Accounts_Users.UserID == '" + UserID + "'";
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserID,UserName,Password,TrueName,DepID,Sex,Birth,Addr,Tel,Email,QQ,School,InputTime,DriveTime,HomeTel,State,RoleId ,CarType");
            strSql.Append("  from  Accounts_Users left join  Accounts_Roles  on Car.Id=Reserve.CarId");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet AccountsUsersList = SqlHelper.Query(strSql.ToString());
            Model.Accounts_Users model = new Model.Accounts_Users();
            model.UserID = Convert.ToInt32(AccountsUsersList.Tables[0].Rows[0]["UserID"].ToString());
            model.Sex = AccountsUsersList.Tables[0].Rows[0]["Sex"].ToString();
            model.Tel = AccountsUsersList.Tables[0].Rows[0]["Tel"].ToString();
            return model;
        }


        /// <summary>
        /// 返回BOOL值
        /// </summary>
        public bool IsExistUserName(string Username)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select *");
            strSql.Append(" from  Accounts_Users ");
            if (Username != "")
            {
                strSql.Append(" where UserName='" + Username + "'");
            }
            DataSet getusername = SqlHelper.Query(strSql.ToString());
            if (getusername == null || getusername.Tables[0].Rows.Count <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


    }
}
